
------------------
-- 需求说明
------------------
-- 设计简易版的银行数据库表结构，要求可以完成以下基本功能
-- 银行开户（注册个人信息）及开卡（办理银行卡）（一个人可以办理多张银行卡，但最多办理三张）
-- 存钱
-- 查询余额
-- 取钱
-- 转账
-- 查看交易记录
-- 账户挂失
-- 账户注销

------------------
-- 表结构说明
------------------
-- 账户信息表
-- 银行卡表
-- 交易信息表（存储存钱和取钱的记录）
-- 转账信息表（存储转账信息记录）
-- 状态信息变化表（存储银行卡状态：1正常；2挂失；3冻结；4注销；5睡眠；）

--------------------
-- 创建库
--------------------
create database Bank;
go

use Bank;
go



--------------------
-- 创建表
-------------------
-- 创建账号信息表
create table AccountInfo(
	AccountId int primary key identity(1, 1), -- 账户编号
	AccountCode varchar(20) not null, -- 身份证号码
	AccountPhone varchar(20) not null, -- 电话号码
	RealName varchar(20) not null, -- 真实姓名
	OpenTime smalldatetime not null, -- 开户时间
);
-- 创建银行表
create table BankCard(
	CardNo varchar(30) primary key, -- 银行卡号
	AccountId int not null,
	CardPwd varchar(30) not null, -- 银行密码
	CardBalance money not null default(0.00), -- 银行卡余额
	CardState tinyint not null default(1), -- 银行卡状态
	CardTime smalldatetime default(getdate()) -- 开卡时间
);
-- 创建交易信息表
create table CardExchange(
	ExchangeId int primary key identity(1, 1),
	CardNo varchar(30) not null, -- 银行卡号（与银行卡号形成主外键关系）
	MoneyInBank money not null, -- 存钱金额
	MoneyOutBank money not null, -- 取钱金额
	ExchangeTime smalldatetime not null, -- 交易时间 
);
-- 创建转账信息表
create table CardTransfer(
	TransferId int primary key identity(1, 1), -- 转账id
	CardNoOut varchar(30) not null, -- 转出银行卡号（与银行卡表形成主外键关系）
	CardNoIn varchar(30) not null, -- 转入银行卡号（与银行卡表形成主外键关系）
	TransferMoney money not null, -- 转账金额
	TransferTime smalldatetime not null, -- 转账时间
);
-- 创建状态信息变化表
create table CardStateChange(
	StateId int primary key identity(1, 1), -- 自增id 
	CardNo varchar(30) not null, -- 银行卡号（与银行卡表形成主外键关系）
	OldState tinyint not null, -- 银行卡原始状态
	NewState tinyint not null, -- 银行卡新状态
	StateWhy nvarchar(200) not null, -- 状态变化原因
	StateTime smalldatetime not null, -- 状态变化时间
);
go


--------------------
-- 增加约束
-------------------
alter table BankCard add constraint CK_BankCard_CardBalance check(CardBalance >= 0)



--------------------
-- 为刘备，关羽，张飞执行开卡开户的操作
-------------------
-- 刘备身份证：420107198905064135
-- 关羽身份证：420107199507104133
-- 张飞身份证：420107199602034138
insert into AccountInfo (AccountCode, AccountPhone, RealName, OpenTime) 
	values ('420107198905064135', '13554785425', '刘备', GETDATE());
insert into BankCard (CardNo, AccountId, CardPwd, CardBalance, CardState) 
	values ('6225125478544587', 1, '123456', 0, 1);
insert into BankCard (CardNo, AccountId, CardPwd, CardBalance, CardState) 
	values ('6225125478544588', 1, '123456', 0, 1);
insert into BankCard (CardNo, AccountId, CardPwd, CardBalance, CardState) 
	values ('6225125478544589', 1, '123456', 0, 1);

insert into AccountInfo (AccountCode, AccountPhone, RealName, OpenTime) 
	values ('420107199507104133', '13454788854', '关羽', GETDATE());
insert into BankCard (CardNo, AccountId, CardPwd, CardBalance, CardState) 
	values ('6225547858741263', 2, '123456', 0, 1);
insert into BankCard (CardNo, AccountId, CardPwd, CardBalance, CardState) 
	values ('6225547858741264', 2, '123456', 0, 1);

insert into AccountInfo (AccountCode, AccountPhone, RealName, OpenTime) 
	values ('420107199602034138', '13456896321', '张飞', GETDATE());
insert into BankCard (CardNo, AccountId, CardPwd, CardBalance, CardState) 
	values ('6225547854125656', 3, '123456', 0, 1);
insert into BankCard (CardNo, AccountId, CardPwd, CardBalance, CardState) 
	values ('6225547854125657', 3, '123456', 0, 1);

--------------------
-- 进行存钱操作，刘备存储2000元，关羽存钱8000元，张飞存钱50万。
-------------------
-- select * from BankCard left join AccountInfo on BankCard.AccountId = AccountInfo.AccountId;
-- select * from CardExchange;
update BankCard set CardBalance = CardBalance + 2000 where CardNo = '6225125478544587';
insert into CardExchange (CardNo, MoneyInBank, MoneyOutBank, ExchangeTime)
	values ('6225125478544587', 2000, 0, GETDATE());

update BankCard set CardBalance = CardBalance + 8000 where CardNo = '6225547858741263';
insert into CardExchange (CardNo, MoneyInBank, MoneyOutBank, ExchangeTime)
	values ('6225547858741263', 8000, 0, GETDATE());

update BankCard set CardBalance = CardBalance + 500000 where CardNo = '6225547854125656';
insert into CardExchange (CardNo, MoneyInBank, MoneyOutBank, ExchangeTime)
	values ('6225547854125656', 500000, 0, GETDATE());


--------------------
-- 进行转账操作，刘备给张飞转1000元
-------------------
-- select * from BankCard left join AccountInfo on BankCard.AccountId = AccountInfo.AccountId;
-- select * from CardTransfer;
update BankCard set CardBalance = CardBalance - 1000 where CardNo = '6225125478544587';
update BankCard set CardBalance = CardBalance + 1000 where CardNo = '6225547854125656';
insert into CardTransfer (CardNoOut, CardNoIn, TransferMoney, TransferTime) 
	values ('6225125478544587', '6225547854125656', 1000, GETDATE());


--------------------
-- 初始化其他人账户信息
-------------------
insert into AccountInfo (AccountCode, AccountPhone, RealName, OpenTime) 
	values ('420107199703035139', '13854785435', '赵云', GETDATE());
insert into BankCard (CardNo, AccountId, CardPwd, CardBalance, CardState) 
	values ('6225125478545001', @@IDENTITY, '123456', 0, 1);

insert into AccountInfo (AccountCode, AccountPhone, RealName, OpenTime) 
	values ('420107199706106240', '13954786546', '马超', GETDATE());
insert into BankCard (CardNo, AccountId, CardPwd, CardBalance, CardState) 
	values ('6225125478546002', @@IDENTITY, '123456', 0, 1);

insert into AccountInfo (AccountCode, AccountPhone, RealName, OpenTime) 
	values ('420107198006107351', '13654787435', '黄忠', GETDATE());
insert into BankCard (CardNo, AccountId, CardPwd, CardBalance, CardState) 
	values ('6225125478547112', @@IDENTITY, '123456', 0, 1);

insert into AccountInfo (AccountCode, AccountPhone, RealName, OpenTime) 
	values ('420107200006108412', '13654787566', '诸葛亮', GETDATE());
insert into BankCard (CardNo, AccountId, CardPwd, CardBalance, CardState) 
	values ('6225125478548233', @@IDENTITY, '123456', 0, 1);

insert into AccountInfo (AccountCode, AccountPhone, RealName, OpenTime) 
	values ('420107200006109523', '13654787677', '马良', GETDATE());
insert into BankCard (CardNo, AccountId, CardPwd, CardBalance, CardState) 
	values ('6225125478549344', @@IDENTITY, '123456', 0, 1);

insert into AccountInfo (AccountCode, AccountPhone, RealName, OpenTime) 
	values ('420107200006120634', '13654788789', '法正', GETDATE());
insert into BankCard (CardNo, AccountId, CardPwd, CardBalance, CardState) 
	values ('6225125478560455', @@IDENTITY, '123456', 0, 1);

insert into AccountInfo (AccountCode, AccountPhone, RealName, OpenTime) 
	values ('420107199908122234', '13854788790', '刘封', GETDATE());




--------------------
-- 进行存钱操作，赵云存储5000元，马超存钱200000元，黄忠存钱50000万，诸葛亮存钱30000。
-------------------
update BankCard set CardBalance = CardBalance + 5000 where CardNo = '6225125478545001';
insert into CardExchange (CardNo, MoneyInBank, MoneyOutBank, ExchangeTime)
	values ('6225125478545001', 5000, 0, GETDATE());

update BankCard set CardBalance = CardBalance + 200000 where CardNo = '6225125478546002';
insert into CardExchange (CardNo, MoneyInBank, MoneyOutBank, ExchangeTime)
	values ('6225125478546002', 200000, 0, GETDATE());

update BankCard set CardBalance = CardBalance + 50000 where CardNo = '6225125478547112';
insert into CardExchange (CardNo, MoneyInBank, MoneyOutBank, ExchangeTime)
	values ('6225125478547112', 50000, 0, GETDATE());

update BankCard set CardBalance = CardBalance + 30000 where CardNo = '6225125478548233';
insert into CardExchange (CardNo, MoneyInBank, MoneyOutBank, ExchangeTime)
	values ('6225125478548233', 30000, 0, GETDATE());

--------------------
-- 进行取钱操作，马超取钱10000元，诸葛亮取钱3000。
-------------------
update BankCard set CardBalance = CardBalance - 10000 where CardNo = '6225125478546002';
insert into CardExchange (CardNo, MoneyInBank, MoneyOutBank, ExchangeTime)
	values ('6225125478546002', 0, 10000, GETDATE());

update BankCard set CardBalance = CardBalance - 3000 where CardNo = '6225125478548233';
insert into CardExchange (CardNo, MoneyInBank, MoneyOutBank, ExchangeTime)
	values ('6225125478548233', 0, 3000, GETDATE());

--------------------
-- 进行转账操作，张飞给黄忠转账50000，
-------------------
-- select * from BankCard left join AccountInfo on BankCard.AccountId = AccountInfo.AccountId;
-- select * from CardTransfer;
update BankCard set CardBalance = CardBalance - 50000 where CardNo = '6225547854125656';
update BankCard set CardBalance = CardBalance + 50000 where CardNo = '6225125478547112';
insert into CardTransfer (CardNoOut, CardNoIn, TransferMoney, TransferTime) 
	values ('6225547854125656', '6225125478547112', 50000, GETDATE());


--------------------
-- 进行冻结操作，马良的卡被冻结了
-------------------
update BankCard set CardState = 3 where CardNo = '6225125478549344'
insert into CardStateChange (CardNo,OldState, NewState, StateWhy, StateTime) 
	values ('6225125478549344', 1, 3, '进行走私，冻结账户', GETDATE());


select * from AccountInfo
select * from BankCard
select * from CardExchange
select * from CardTransfer
select * from CardStateChange

print '执行成功'

-- 关羽的银行卡号为 6225547858741263，查询出余额比关羽多的银行卡信息，显示卡号，身份证，姓名，余额。
select * from BankCard where CardNo = '6225547858741263';
select BankCard.CardNo,AccountInfo.AccountCode,AccountInfo.RealName,BankCard.CardBalance from AccountInfo
	inner join BankCard on AccountInfo.AccountId = BankCard.AccountId
	where CardBalance > '8000.00';

-- 查询有取款记录的银行卡及账户信息，显示卡号，身份证，姓名，余额
select BankCard.CardNo,AccountInfo.AccountCode,AccountInfo.RealName,BankCard.CardBalance from AccountInfo
	inner join BankCard on AccountInfo.AccountId = BankCard.AccountId
	inner join CardExchange on BankCard.CardNo = CardExchange. CardNo 
	where MoneyOutBank > '0';

-- 查询出没有开卡的账户信息。
select BankCard.CardNo,AccountInfo.AccountCode,AccountInfo.RealName,BankCard.CardBalance from AccountInfo
	left join BankCard on AccountInfo.AccountId = BankCard.AccountId
	where CardNo is null;

-- 查询出没有存款记录的银行卡及账户信息，显示卡号，身份证，姓名，余额
select BankCard.CardNo,AccountInfo.AccountCode,AccountInfo.RealName,BankCard.CardBalance from AccountInfo
	inner join BankCard on AccountInfo.AccountId = BankCard.AccountId
	inner join CardExchange on BankCard.CardNo = CardExchange. CardNo 
	where MoneyInBank = '0';

-- 关羽的银行卡号为 6225547858741263，查询当天是否有收到转账
select *from BankCard
	inner join CardTransfer on BankCard.CardNo = CardTransfer.CardNoIn 
	where BankCard.CardNo = '6225547858741263';


-- 从所有银行卡中查询出余额最高的银行卡的交易明细（存钱取钱信息）   
select  top 1 CardExchange.* , BankCard.CardBalance  from BankCard
	inner join CardExchange on BankCard.CardNo = CardExchange.CardNo
	order by CardBalance desc

-- 查询出交易次数（存取款操作）最多的银行卡账户信息，显示：卡号、身份证、姓名、余额、交易次数
select top 1 BankCard.CardNo,AccountInfo.AccountCode,AccountInfo.RealName,BankCard.CardBalance,count(0)from AccountInfo 
	inner join BankCard on AccountInfo.AccountId = BankCard.AccountId
	inner join CardExchange on BankCard.CardNo = CardExchange.CardNo 
	group by BankCard.CardNo,AccountInfo.AccountCode,AccountInfo.RealName,BankCard.CardBalance
	order by count(0)desc;


-- 查询出没有转账交易记录的银行卡账户信息, 显示：卡号、身份证、姓名、余额
select BankCard.CardNo,AccountCode,RealName from AccountInfo
	inner join BankCard on BankCard.AccountId=AccountInfo.AccountId
	where CardNo not in (select CardNoOut from CardTransfer) and
	CardNo not in (select CardNoIn from CardTransfer);

-- 查询刘备的银行卡信息，显示卡号、身份证、姓名、余额。
select BankCard.CardNo,AccountInfo.AccountCode,AccountInfo.RealName,BankCard.CardBalance from AccountInfo 
	inner join BankCard on AccountInfo.AccountId = BankCard.AccountId
	inner join CardExchange on BankCard.CardNo = CardExchange. CardNo 
where AccountInfo.RealName = '刘备';

-- 查询银行卡 6225125478544587 的存钱记录，显示卡号、存钱金额、存钱时间、户主姓名。
select BankCard.CardNo,CardExchange.MoneyInBank,CardExchange.ExchangeTime,AccountInfo.RealName from AccountInfo 
	inner join BankCard on AccountInfo.AccountId = BankCard.AccountId
	inner join CardExchange on BankCard.CardNo = CardExchange. CardNo 
	where BankCard.CardNo = '6225125478544587';

